-- @owner: opentestcase027
-- @date: 2022/6/16
-- @testpoint:验证concat函数支持下推

--step1: 建表     expect:建表成功
drop table if exists t_ustore_concat_0001_37 cascade;
drop table if exists t_ustore_concat_0002_37 cascade;
create table t_ustore_concat_0001_37 (
    row_tinyint		     tinyint  default 2,
    row_smallint	     smallint ,
    row_int			     integer not null,
    row_binary_int       binary_integer not null,
    row_bigint		     bigint,
    row_money            decimal,
    row_numeric		     numeric(18,2),
    row_number           number,
    row_smallserial      smallint not null,
    row_serial           int not null,
    row_bigserial        bigint not null,
    row_real		     real,
    row_float4           float,
    row_double		     double precision,
    row_float8           float,
    row_float            float,
    row_binary_double    binary_double,
    row_decimal          decimal,
    row_dec              decimal,
    row_integer          integer,
    row_varchar          varchar(300),
    row_char		     char(300),
    row_text		     text default '多列统计信息，需要覆盖各种数据类型，tpcds中数据类型不全，对不存在的数据需要额外构造多列统计信息。',
    row_timestamp		 timestamp with time zone,
    row_timestamptz		 timestamp without time zone,
    row_date		     date,
    row_time		     timestamp without time zone,
    row_timetz		     timestamp with time zone,
    row_interval	     interval day to second,
    row_boolean			 boolean default 0,
    row_array			 int
) 
WITH (STORAGE_TYPE=USTORE) partition by range(row_int,row_decimal)
(partition p1 values less than(10,100),
partition p2 values less than(50,500),
partition p3 values less than(100,800),
partition p4 values less than(maxvalue,maxvalue)) ;

create table t_ustore_concat_0002_37 (
    col_tinyint		     tinyint  default 2,
    col_smallint	     smallint ,
    col_int			     integer,
    col_binary_int       binary_integer ,
    col_bigint		     bigint,
    col_money            decimal,
    col_numeric		     numeric(18,2),
    col_number           number,
    col_smallserial      smallint,
    col_serial           int ,
    col_bigserial        bigint,
    col_real		     real,
    col_float4           float,
    col_double		     double precision,
    col_float8           float,
    col_float            float,
    col_binary_double    binary_double,
    col_decimal          decimal,
    col_dec              decimal,
    col_integer          integer,
    col_varchar          varchar(300),
    col_char		     char(300),
    col_text		     text default '多列统计信息，需要覆盖各种数据类型，tpcds中数据类型不全，对不存在的数据需要额外构造多列统计信息',
    col_timestamp		 timestamp with time zone,
    col_timestamptz		 timestamp without time zone,
    col_date		     date,
    col_time		     timestamp without time zone,
    col_timetz		     timestamp with time zone,
    col_interval	     interval day to second,
    col_boolean			 boolean default 0
)
WITH (STORAGE_TYPE=USTORE) partition by range(col_int,col_decimal)
(partition p1 values less than(10,100),
partition p2 values less than(50,500),
partition p3 values less than(100,800),
partition p4 values less than(maxvalue,maxvalue)) ;


--step2: 创建序列       expect:成功
drop sequence if exists s_seq;
create sequence s_seq increment by 1 start with 100;

--step3: 插入数据       expect:成功
insert into t_ustore_concat_0001_37 select s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval,s_seq.nextval||'A','B'||s_seq.nextval,s_seq.nextval||'A',
'2017-12-6 11:52:00.1235','2017-12-6 11:52:00.1235','2017-12-6','2017-12-6 11:52:00.1235','2017-12-6 11:52:00.1235', (interval '1' day), 1,0;
  
insert into t_ustore_concat_0001_37(row_smallint,row_int,row_binary_int,row_smallserial,row_serial,row_bigserial,row_decimal,row_date) values(-32768,-2147483648,0,32767,32767,32767,1.23456,'2018-06-04');

insert into t_ustore_concat_0001_37(row_smallint,row_int,row_binary_int,row_smallserial,row_serial,row_bigserial,row_decimal,row_date,row_timestamp) values(0,0,0,3276,3276,3276,9.99999,'2018-06-04','2018-06-04 11:15:30');

insert into t_ustore_concat_0001_37(row_smallint,row_int,row_binary_int,row_smallserial,row_serial,row_bigserial,row_decimal,row_date,row_varchar,row_text) values(32767,2147483647,12,-32768,32767,32767,2.23456,'2018-06-04','',
'--多列统计信息，需要覆盖各种数据类型，tpcds中数据类型不全，对不存在的数据需要额外构造
--1)在该用例中，主要验证语法，包括正例、反例
--2)统计信息的正确性在01用例中验证，本用例中重点关注是否有异常报错
--3)本用例主要关注analyze语法
--多列统计信息，需要覆盖各种数据类型，tpcds中数据类型不全，对不存在的数据需要额外构造
--1)在该用例中，主要验证语法，包括正例、反例
--2)统计信息的正确性在01用例中验证，本用例中重点关注是否有异常报错
--3)本用例主要关注analyze语法
--2)统计信息的正确性在01用例中验证，本用例中重点关注是否有异常报错
--3)本用例主要关注analyze语法
--多列统计信息，需要覆盖各种数据类型，tpcds中数据类型不全，对不存在的数据需要额外构造
--1)在该用例中，主要验证语法，包括正例、反例
--2)统计信息的正确性在01用例中验证，本用例中重点关注是否有异常报错
--3)本用例主要关注analyze语法
--多列统计信息，需要覆盖各种数据类型，tpcds中数据类型不全，对不存在的数据需要额外构造
--1)在该用例中，主要验证语法，包括正例、反例
--1)在该用例中，主要验证语法，包括正例、反例
--2)统计信息的正确性在01用例中验证，本用例中重点关注是否有异常报错
--3)本用例主要关注analyze语法');

insert into t_ustore_concat_0002_37 select
row_tinyint		   ,  
row_smallint	   , 
row_int			   , 
row_binary_int     , 
row_bigint		   , 
row_money          , 
row_numeric		   , 
row_number         , 
row_smallserial    , 
row_serial         , 
row_bigserial      , 
row_real		   , 
row_float4         , 
row_double		   , 
row_float8         , 
row_float          , 
row_binary_double  , 
row_decimal        , 
row_dec            , 
row_integer        , 
row_varchar        , 
row_char		   , 
row_text		   , 
row_timestamp		,
row_timestamptz		,
row_date		   , 
row_time		   , 
row_timetz		   , 
row_interval	   , 
row_boolean from t_ustore_concat_0001_37;

--step4: 更新数据表       expect:成功
update t_ustore_concat_0001_37 set row_array=2 where row_tinyint < 50;

--step5: 验证各种数据类型，可下推       expect:成功
select 
concat(row_tinyint		   ,  
row_smallint	   , 
row_int			   , 
row_binary_int     , 
row_bigint		   , 
row_money          , 
row_numeric		   , 
row_number         , 
row_smallserial    , 
row_serial         , 
row_bigserial      , 
row_real		   , 
row_float4         , 
row_double		   , 
row_float8         , 
row_float          , 
row_binary_double  , 
row_decimal        , 
row_dec            , 
row_integer        , 
row_varchar        , 
row_char		   , 
 
row_timestamp		,
row_timestamptz		,
row_date		   , 
row_time		   , 
row_timetz		    
) from t_ustore_concat_0001_37 where row_tinyint between 1 and 103;

--step6: 包含array类型，不下推      expect:成功
select concat(row_tinyint,  row_array) from t_ustore_concat_0001_37 where row_tinyint between 1 and 103 and row_array is not null;
select concat_ws('''',row_tinyint		   ,  
row_smallint	   , 
row_int			   , 
row_binary_int     , 
row_bigint		   , 
row_money          , 
row_numeric		   , 
row_number         , 
row_smallserial    , 
row_serial         , 
row_bigserial      , 
row_real		   , 
row_float4         , 
row_double		   , 
row_float8         , 
row_float          , 
row_binary_double  , 
row_decimal        , 
row_dec            , 
row_integer        , 
row_varchar        , 
row_char		   , 
row_text		   , 
row_timestamp		,
row_timestamptz		,
row_date		   , 
row_time		   , 
row_timetz		   , 
row_interval	   , 
row_boolean) from t_ustore_concat_0001_37 where row_tinyint between 1 and 103;

--step7: 查看执行计划     expect:成功
--set explain_perf_mode =normal;
explain select c1 from 
(select concat(row_text,row_text,row_text,row_text,row_text,row_text,row_text,row_text)c1 
from t_ustore_concat_0001_37
group by concat(row_text,row_text,row_text,row_text,row_text,row_text,row_text,row_text) order by 1 desc limit 1) group by c1;

explain select concat(null,col_char,col_text,null,col_varchar) from t_ustore_concat_0002_37
where  ('2011-1-1'<='2011-1-1')or ('2010-1-1'<='2012-1-1');

explain select concat(current_date) from t_ustore_concat_0001_37 where row_tinyint>59;

--step8: 清理环境     expect:成功
drop table if exists t_ustore_concat_0001_37;
drop table if exists t_ustore_concat_0002_37;
